/*
 * Created on 02-Oct-2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package wstrader.task.traffic.physical;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import wstrader.task.trade.TradeTask;
import pc.sql.value.*;
import pc.system.Record;
import pc.system.Tasks;
import pc.util.Utils;

/**
 * @author NANSA
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class Contract extends TradeTask
{

	  public Contract(Tasks owner)
	  {
	    super(owner);
	  }
	
	  public long doTask()
	  {
	    String table_name = "contracts";
	    int task_id     = this.getRequest().getIntValue("task_id");

	    long focused_id = this.getRequest().getLongValue("contracts");
	    switch (task_id)
		{
	      case 1 : // Update

	        break;
	      case 2 : // Delete
	        focused_id = doDelete(table_name,focused_id);
	        focused_id = 1;
	        break;
	      case 3: // allocate
	    	  String CntType = "";
	    	  if (getRequest().getStringValue("contract_type").compareTo("Sales Contract")==0)
	    	  {
	    		  CntType ="S";
	    	  }
	    	  else
	    	  {
	    		  CntType = "P";
	    	  }
	    	  updateAllocationDetails(focused_id,CntType);
	    	  focused_id=focused_id;	
	    	  break;	        
	      case 4: // allocate
	    	  updateImportDetails(focused_id);
	    	  focused_id=focused_id;	
	    	  break;
	      case 5:
	    	  int compexpflg = getRequest().getIntValue("comp_exp");
	    	  updateCompStatus("E",focused_id,compexpflg);
	    	  updateInvoiceField(focused_id);
	    	  break;
	      case 6:
	    	  int compimpflg = getRequest().getIntValue("comp_imp");
	    	  updateCompStatus("I",focused_id,compimpflg);
	    	  updatePendingReceiveField(focused_id);
	    	  break;
	    	  
	      default : // View only
	        break;
	    }
	    this.setFocusedID(focused_id);

	    return focused_id;
	  }  
	  
	  public ResultSet fetchContractList()
	  {
	  
	  		String CntType = getRequest().getStringValue("cnt_type");
	  		if (CntType.compareTo("")== 0) CntType = "0";
	  		int OriginId = getRequest().getIntValue("origin");
	  		int GradeId = getRequest().getIntValue("grade");	  	
	  		int BuySellId = getRequest().getIntValue("buyer_seller");
	  		java.sql.Date LsDt = getRequest().getValueDate("ls_date");
	  	
	  		int QualityId = getRequest().getIntValue("quality");
	  		String Sql="";
	  	
	  		Sql ="Select * from contracts where " +
			"(type = ? OR ?) " +
	        "and (origin_id = ? OR ?) "+
			"and (grade_id = ? OR ?) " +
			"and (supplier_buyer_id = ? OR ?) " +
			"and (quality_id = ? OR ?) " +
			"and ((last_date < ? OR ?) OR (last_date IS NULL)) " +
			"order by id desc";
	  		Vector vp = new Vector();

	  		vp.addElement(new StringValue(CntType));
	  		vp.addElement(new BooleanValue(CntType.compareTo("0") == 0));

	  		vp.addElement(new IntValue(OriginId));
	  		vp.addElement(new BooleanValue(OriginId == 0));

	  		vp.addElement(new IntValue(GradeId));
	  		vp.addElement(new BooleanValue(GradeId == 0));

	  		vp.addElement(new IntValue(BuySellId));
	  		vp.addElement(new BooleanValue(BuySellId == 0));

	  		vp.addElement(new IntValue(QualityId));
	  		vp.addElement(new BooleanValue(QualityId == 0));	  		
	
	  		vp.addElement(new DateValue(LsDt));
	  		vp.addElement(new BooleanValue(LsDt.compareTo(new java.sql.Date(0)) == 0));

	  		return getSQLCommand().executeQuery(Sql,vp);	  	
	  }
	  
	  public ResultSet fetchImportList()
	  {
	  	int CntId = getRequest().getIntValue("contracts");
	  	String Sql = "Select * from weight_note where contract_id= " + CntId + " order by id";
	  	return getSQLCommand().executeQuery(Sql);
	  }

	  public ResultSet fetchTraffic_Cnt_AllocationList(String CntType)
	  {
	  	int CntId = getRequest().getIntValue("contracts");
	  	//System.out.println("Contract Type-"+CntType);
	  	String Sql="";
	  	if (CntType.compareTo("P")==0)
	  	{
	  		Sql = "Select * from contract_allocation where contract_pid= " + CntId + " order by id";
	  	}
	  	else
	  	{
	  		Sql = "Select * from contract_allocation where contract_sid= " + CntId + " order by id";
	  	}
	  	//System.out.println("CntAllcSQL-"+Sql);
	  	return getSQLCommand().executeQuery(Sql);
	  }
	  
	  public long updateImportDetails(long Id)
	  {
		  	int RowUpd = getRequest().getIntValue("sel_row");
		  	System.out.println("RowUpd----"+RowUpd);
			long WgtId = getRequest().getLongValue("wn_id_"+RowUpd);
	      	String WNRef = getRequest().getStringValue("ref_number_"+RowUpd);//this.getNew_WNRef();
	      	String Type = "I";
	      	java.sql.Date DelvyDt = Utils.strToDate(getRequest().getStringValue("date_"+RowUpd));
	      	int GradeId = getRequest().getIntValue("grade");
	      	String Loc = getRequest().getStringValue("location_"+RowUpd);
	      	float GrossWgt = 0;
	      	float TareWgt = 0;
	      	float NetWgt = getRequest().getFloatValue("net_weight_"+RowUpd);
	      	float NoOfBags = getRequest().getFloatValue("no_of_bags_"+RowUpd);
	      	float StkWgt = getRequest().getFloatValue("net_weight_"+RowUpd);
	      	float StkBag = getRequest().getFloatValue("no_of_bags_"+RowUpd);
	      	long RecExist = this.findID("weight_note","id",WgtId);
	     
	      	String SqlStr="";
	      	if (RecExist > 0)
	      	{
	     		SqlStr ="update weight_note " +
	    		"SET ref_number = ?, " +
	    		"date = ?, " +
	    		"type = ?, " +
	    		"grade_id = ?, " +
	    		"location = ?, " +
	    		"gross_weight = ?, " +
	    		"tare_weight = ?, " +
	    		"net_weight = ?, " +
	    		"no_of_bags = ?, " +
	    		"stock_weight = ?, " +
	    		"stock_bags = ?, " +
	    		"contract_id = ?, " +
	    		"inst_id = ? " +
	    		"where id = ?";	
	      		Vector vp = new Vector();
	      		vp.addElement(new StringValue(WNRef));
	      		vp.addElement(new DateValue(DelvyDt));
	      		vp.addElement(new StringValue(Type));
	      		vp.addElement(new IntValue(GradeId));
	      		vp.addElement(new StringValue(Loc));
	      		vp.addElement(new FloatValue(GrossWgt));
	      		vp.addElement(new FloatValue(TareWgt));
	      		vp.addElement(new FloatValue(NetWgt));
	      		vp.addElement(new FloatValue(NoOfBags));
	      		vp.addElement(new FloatValue(StkWgt));
	      		vp.addElement(new FloatValue(StkBag));
	      		vp.addElement(new LongValue(Id));     
	      		vp.addElement(new LongValue(0));      		
	      		vp.addElement(new LongValue(WgtId));
	      		int ret = getSQLCommand().executeUpdate(SqlStr,vp);	  		
	      	}
	      	else
	      	{
	      		SqlStr ="insert weight_note " +
	      	    		"SET ref_number = ?, " +
	      	    		"date = ?, " +
	      	    		"type = ?, " +
	      	    		"grade_id = ?, " +
	      	    		"location = ?, " +
	      	    		"gross_weight = ?, " +
	      	    		"tare_weight = ?, " +
	      	    		"net_weight = ?, " +
	      	    		"no_of_bags = ?, " +
	      	    		"stock_weight = ?, " +
	      	    		"stock_bags = ?, " +
	      	    		"contract_id = ?, " +
	      	    		"inst_id = ?";
	      			Vector vp = new Vector();
	          		vp.addElement(new StringValue(WNRef));
	          		vp.addElement(new DateValue(DelvyDt));
	          		vp.addElement(new StringValue(Type));
	          		vp.addElement(new IntValue(GradeId));
	          		vp.addElement(new StringValue(Loc));
	          		vp.addElement(new FloatValue(GrossWgt));
	          		vp.addElement(new FloatValue(TareWgt));
	          		vp.addElement(new FloatValue(NetWgt));
	          		vp.addElement(new FloatValue(NoOfBags));
	          		vp.addElement(new FloatValue(StkWgt));
	          		vp.addElement(new FloatValue(StkBag));
	          		vp.addElement(new LongValue(Id));
	          		vp.addElement(new LongValue(0));
	      			int ret = getSQLCommand().executeUpdate(SqlStr,vp);	  		
	      	}	

			  return Id;
	  }
	  
	  public long updateAllocationDetails(long Id,String Type)
	  {
		  	int RowUpd = getRequest().getIntValue("sel_row_alc");
			long AlcId = getRequest().getLongValue("alc_id_"+RowUpd);
		  	System.out.println("AlcId----"+AlcId);
			int QualityId = getRequest().getIntValue("quality");
			int GradeId = getRequest().getIntValue("grade");
			long Pid=0;
			long Sid=0;
			if (Type.compareTo("S")==0)
			{
				Pid = this.getFieldValueForString("contracts", "ref_number", getRequest().getStringValue("alc_ref_number_"+RowUpd));
				Sid = getRequest().getLongValue("contracts");
			}
			else
			{
				Sid = this.getFieldValueForString("contracts", "ref_number", getRequest().getStringValue("alc_ref_number_"+RowUpd));
				Pid = getRequest().getLongValue("contracts");				
			}
	      	java.sql.Date ExpDt = Utils.strToDate(getRequest().getStringValue("alc_date_"+RowUpd));
	      	String Loc = getRequest().getStringValue("alc_location_"+RowUpd);
	      	float NetWgt = getRequest().getFloatValue("allocated_tons_"+RowUpd);
	      	float NoOfBags = getRequest().getFloatValue("allocated_bags_"+RowUpd);
	      	long RecExist = this.findID("contract_allocation","id",AlcId);
	     
	      	String SqlStr="";
	      	if (RecExist > 0)
	      	{
	     		SqlStr ="update contract_allocation " +
	    		"SET contract_pid = ?, " +
	    		"contract_sid = ?, " +
	    		"quality_id = ?, " +
	    		"grade_id = ?, " +
	    		"export_date = ?, " +
	    		"location = ?, " +
	    		"allocated_tons = ?, " +
	    		"allocated_bags = ?, " +
	    		"user_id = ?, " +
	    		"date = ?, " +
	    		"time = ? " +
	    		"where id = ?";	
	      		Vector vp = new Vector();
	      		vp.addElement(new LongValue(Pid));
	      		vp.addElement(new LongValue(Sid));
	      		vp.addElement(new LongValue(QualityId));
	      		vp.addElement(new LongValue(GradeId));
	      		vp.addElement(new DateValue(ExpDt));
	      		vp.addElement(new StringValue(Loc));
	      		vp.addElement(new FloatValue(NetWgt));
	      		vp.addElement(new FloatValue(NoOfBags));
	      		vp.addElement(new IntValue(this.getUser().getUserID()));     
	      		vp.addElement(new DateValue(Utils.today()));      		
	      		vp.addElement(new TimeValue(Utils.now()));
	      		vp.addElement(new LongValue(AlcId));
	      		int ret = getSQLCommand().executeUpdate(SqlStr,vp);	  		
	      	}
	      	else
	      	{
	      		SqlStr ="insert contract_allocation " +
			    "SET contract_pid = ?, " +
			    "contract_sid = ?, " +
			    "quality_id = ?, " +
			    "grade_id = ?, " +
			    "export_date = ?, " +
			    "location = ?, " +
			    "allocated_tons = ?, " +
			    "allocated_bags = ?, " +
			    "user_id = ?, " +
			    "date = ?, " +
			    "time = ?";
			     Vector vp = new Vector();
			     vp.addElement(new LongValue(Pid));
			     vp.addElement(new LongValue(Sid));
			     vp.addElement(new LongValue(QualityId));
			     vp.addElement(new LongValue(GradeId));
			     vp.addElement(new DateValue(ExpDt));
			     vp.addElement(new StringValue(Loc));
			     vp.addElement(new FloatValue(NetWgt));
			     vp.addElement(new FloatValue(NoOfBags));
			     vp.addElement(new IntValue(this.getUser().getUserID()));     
			     vp.addElement(new DateValue(Utils.today()));      		
			     vp.addElement(new TimeValue(Utils.now()));
			     int ret = getSQLCommand().executeUpdate(SqlStr,vp);	
	      	}
	      	
	      	float allocatedtons = this.getSum("allocated_tons", "contract_sid", "contract_allocation", Id);
	      	String UpdSql = "update contracts " +
	      	"set allocated_tons = ?, " + 
	      	"inv_tons = ?, "+
	      	"shipped_tons = ? " +
	      	"where id =?";
	      	Vector vpalc = new Vector();
		    vpalc.addElement(new FloatValue(allocatedtons));
		    vpalc.addElement(new FloatValue(allocatedtons));
		    vpalc.addElement(new FloatValue(allocatedtons));
		    vpalc.addElement(new LongValue(Id));
		    
		    int retalc = getSQLCommand().executeUpdate(UpdSql,vpalc);	

			return Id;
	  }
	  
	  
        public ResultSet getContractRefList()
        {
        	
        	String Sql="";
       		Sql ="select id,contract_no,ref_number,tons,receive_tons,unfixed_tons from contracts order by id";
    	  	return getSQLCommand().executeQuery(Sql);        	
        }	
        
        public ResultSet getContractNoList()
        {
        	int gradeid= getRequest().getIntValue("grade");
        	String Sql="";
       		Sql ="select ref_number as id,ref_number as name from contracts where type = 'P' and comp_imp=0 order by id";
    	  	return getSQLCommand().executeQuery(Sql);        	
        }        
        
  	  public String getNew_WNRef()
  	  {
  		int dy = Utils.getCurYear() % 100;
  		String yrs = Utils.formatNumber(dy,2);
  	    String sql = "SELECT MAX(ref_number) as ref_number FROM weight_note";
  	    Record rc = new Record(this.getSQLCommand().executeQuery(sql));
  	    String ref = (!rc.isEmpty()) ? rc.getString("ref_number") : "SWC-BD-WR "+ yrs + "-00000";
  	    int RefNo=Utils.strToInt(ref.substring(13,18))+1;
  	  
  	    if (Utils.numberToStr(RefNo,0,false).length()==5) ref = Utils.numberToStr(RefNo,0,false);
  	    if (Utils.numberToStr(RefNo,0,false).length()==4) ref = "0" + Utils.numberToStr(RefNo,0,false);
  	    if (Utils.numberToStr(RefNo,0,false).length()==3) ref = "00" + Utils.numberToStr(RefNo,0,false);
  	    if (Utils.numberToStr(RefNo,0,false).length()==2) ref = "000" + Utils.numberToStr(RefNo,0,false);
  	    if (Utils.numberToStr(RefNo,0,false).length()==1) ref = "0000" + Utils.numberToStr(RefNo,0,false);
    
  	    System.out.println("SWC-BD-WR " + yrs + ref);
  	    rc.close();
  	    return "SWC-BD-WR " + yrs + "-"+ref;//this.getNextRefNumber(ref);
  	  }
  	  
  	  public float chkRefNumber(String RefNo)
  	  {
  		  float Avlton =0;
  		  System.out.println("Requested No-----------"+RefNo);
  		  long Pid = this.getFieldValueForString("contracts", "ref_number", RefNo);
  		  ResultSet Rs = getSQLCommand().executeQuery("Select sum(allocated_tons) as tons from contract_allocation where contract_pid = "+Pid);
  		  float CntTon = Utils.strToFloat(this.getFieldValueForId("contracts", "tons", (int) Pid));
  		  try {
			Rs.beforeFirst();
			Rs.next();
			Avlton = CntTon-Rs.getFloat("tons");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println(e.getMessage());
		}
  		  
  		  return Avlton;
  	  }
  	  
  	  public long updateCompStatus(String Type, long id,int curflg)
  	  {
  		  if (Type.compareTo("I")==0)
  		  {
  			  if (curflg==0)
  			  {
  			  getSQLCommand().executeUpdate("Update contracts set comp_imp = 1 where id = "+id);
  			  
  			  int GradeId = getRequest().getIntValue("grade");
  			  float AdjWgt = getRequest().getFloatValue("tons") - getRequest().getFloatValue("ImpTon");
	      	  String SqlStr ="insert weight_note " +
	      	    		"SET ref_number = ?, " +
	      	    		"date = ?, " +
	      	    		"type = ?, " +
	      	    		"grade_id = ?, " +
	      	    		"location = ?, " +
	      	    		"gross_weight = ?, " +
	      	    		"tare_weight = ?, " +
	      	    		"net_weight = ?, " +
	      	    		"no_of_bags = ?, " +
	      	    		"stock_weight = ?, " +
	      	    		"stock_bags = ?, " +
	      	    		"contract_id = ?, " +
	      	    		"inst_id = ?";
	      			Vector vp = new Vector();
	          		vp.addElement(new StringValue("Adjustment"));
	          		vp.addElement(new DateValue(Utils.today()));
	          		vp.addElement(new StringValue("I"));
	          		vp.addElement(new IntValue(GradeId));
	          		vp.addElement(new StringValue("Adjusttment"));
	          		vp.addElement(new FloatValue(AdjWgt));
	          		vp.addElement(new FloatValue(0));
	          		vp.addElement(new FloatValue(AdjWgt));
	          		vp.addElement(new FloatValue(0));
	          		vp.addElement(new FloatValue(AdjWgt));
	          		vp.addElement(new FloatValue(0));
	          		vp.addElement(new LongValue(id));
	          		vp.addElement(new LongValue(0));
	      			int ret = getSQLCommand().executeUpdate(SqlStr,vp);
  			  }
  			  else
  			  {
  				getSQLCommand().executeUpdate("Update contracts set comp_imp = 0 where id = "+id);
  				getSQLCommand().executeUpdate("delete from weight_note where ref_number = 'Adjustment' and contract_id = "+id);
  			  }
  		  }
  		  else
  		  {
  			  if (curflg==0)
  			  {  			  
  				  getSQLCommand().executeUpdate("Update contracts set comp_exp = 1 where id = "+id);
  			
  				  int GradeId = getRequest().getIntValue("grade");
  				  int QualityId = getRequest().getIntValue("quality");
  				  float AdjWgt = getRequest().getFloatValue("tons") - getRequest().getFloatValue("ExpTon");
  			
  				  String SqlStr ="insert contract_allocation " +
  						  "SET contract_pid = ?, " +
  						  "contract_sid = ?, " +
  						  "quality_id = ?, " +
  						  "grade_id = ?, " +
  						  "export_date = ?, " +
  						  "location = ?, " +
  						  "allocated_tons = ?, " +
  						  "allocated_bags = ?, " +
  						  "user_id = ?, " +
  						  "date = ?, " +
  						  "time = ?";
  				  Vector vp = new Vector();
  				  vp.addElement(new LongValue(0));
  				  vp.addElement(new LongValue(id));
  				  vp.addElement(new LongValue(QualityId));
  				  vp.addElement(new LongValue(GradeId));
  				  vp.addElement(new DateValue(Utils.today()));
  				  vp.addElement(new StringValue("Adjustment"));
  				  vp.addElement(new FloatValue(AdjWgt));
  				  vp.addElement(new FloatValue(0));
  				  vp.addElement(new IntValue(this.getUser().getUserID()));     
  				  vp.addElement(new DateValue(Utils.today()));      		
  				  vp.addElement(new TimeValue(Utils.now()));
  				  int ret = getSQLCommand().executeUpdate(SqlStr,vp);
  			  }
  			  else
  			  {
    				getSQLCommand().executeUpdate("Update contracts set comp_exp = 0 where id = "+id);
      				getSQLCommand().executeUpdate("delete from contract_allocation where location = 'Adjustment' and contract_pid=0 and contract_sid = "+id);
  			  }
  		  }
  		  return 0;
  	  }
}
